# -*- coding = utf-8 -*-
# @Time : 2022/2/11 10:15
# @File : billing system.py
# @Software : PyCharm

import pymysql.cursors

connection = pymysql.connect(host='localhost', user='root', password='root',
                             database='billing_system', charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()


def main():
    while True:
        menu()
        choice = int(input('请输入要操作的功能编号[1-5]:'))
        if choice in [1, 2, 3, 4, 5]:
            if choice == 5:
                answer = input('您确定要退出系统吗？y/n')
                if answer == 'y' or answer == 'Y':
                    print('感谢您的使用！')
                    connection.close()
                    break  # 退出系统
                else:
                    continue
            elif choice == 1:
                add()  # 添加账务
            elif choice == 2:
                modify()  # 编辑财务
            elif choice == 3:
                delete()  # 删除财务
            elif choice == 4:
                search()  # 查询财务


def menu():
    print('--------------------管家婆家庭记账系统----------------------')
    print('1.添加账务\t2.编辑账务\t3.删除账务\t4.查询账务\t5.退出系统\t')


def add():
    category = input('请输入类别:')
    account = input('请输入账户:')
    money = input('请输入金额:')
    add_time = input('请输入时间:')
    note = input('请输入说明:')
    try:
        sql = "INSERT INTO billing_system (category,account,money,time,note) " \
              "VALUES('" + category + "','" + account + "','" + money + "','" + add_time + "','" + note + "')"
        cursor.execute(sql)  # 执行sql语句
        print('添加账务成功！')
    except Exception:
        print('添加账务失败！')
    connection.commit()  # 向数据库提交数据
    connection.close()


def modify():
    userid = input('请输入ID:')
    category1 = input('请输入新类别:')
    account1 = input('请输入新账户:')
    money1 = input('请输入新金额:')
    add_time1 = input('请输入新时间:')
    note1 = input('请输入新说明:')
    try:
        sql = "UPDATE billing_system SET category='" + category1 + "',account='" + account1 + "',money='" + money1 + "',time='" + add_time1 + "',note='" + note1 + "' WHERE id=" + userid + ";"
        cursor.execute(sql)  # 执行sql语句
        print('编辑账务成功！')
    except Exception:
        print('修改财务失败！')
    connection.commit()  # 向数据库提交数据
    connection.close()


def delete():
    userid = input('请输入ID:')
    try:
        sql = "DELETE FROM billing_system WHERE id=" + userid + ""
        cursor.execute(sql)  # 执行sql语句
        print('删除账务成功！')
    except Exception:
        print('删除财务失败！')
    connection.commit()  # 向数据库提交数据
    connection.close()


def search():
    while True:
        mode = input('1.查询所有\t2.按条件查询\t:')
        if mode == '1':
            try:
                sql = "SELECT * FROM billing_system"
                cursor.execute(sql)
                result = cursor.fetchall()
                print('ID 类别 账户 金额     时间              说明\t')
                for i in result:
                    print(i['id'], i['category'], i['account'], i['money'], i['time'], i['note'])
            except Exception:
                print('查询失败！')
            break
        elif mode == '2':
            try:
                start_time = input('请输入查询起始时间： ')
                end_time = input('情输入查询结束时间： ')
                print('ID 类别 账户 金额     时间              说明\t')
                sql = "SELECT * FROM billing_system WHERE time>='" + start_time + "' and time>='" + end_time + "';"
                cursor.execute(sql)
                result = cursor.fetchall()
                for i in result:
                    print(i['id'], i['category'], i['account'], i['money'], i['time'], i['note'])
            except Exception:
                print('查询失败！')
            break
        else:
            break


if __name__ == '__main__':
    main()
